package com.example.mapp;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import com.example.mapp.buildSql.StatisticsMappSql;

/**
 * Created by douyx on 2017/11/13.
 */
@Mapper
public interface StatisticsMapp {

	/**
	 * 辖区内电梯的总数量
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countEleNum")
	Map<String, Object> countEleNum() throws Exception;

	/**
	 * 辖区内电梯数量分布
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countNumDistrib")
	List<Map<String, Object>> countNumDistrib(Map inMap) throws Exception;

	/**
	 * 辖区内电梯类型分布
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countTypeDistrib")
	List<Map<String, Object>> countTypeDistrib() throws Exception;

	/**
	 * 辖区内保障力量分布
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countOperDistrib")
	Map<String, Object> countOperDistrib() throws Exception;

	/**
	 * 辖区内电梯年限分布
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countYearDistrib")
	Map<String, Object> countYearDistrib() throws Exception;

	/**
	 * 辖区内各项安全状况统计
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countSafeNum")
	Map<String, Object> countSafeNum() throws Exception;

	@Insert("INSERT INTO implementation_statistics (year,quarter,feedback_num,rescue_num,maint_num,inspection_num) "
			+ "SELECT YEAR(ADDDATE(NOW(),-1)),QUARTER(ADDDATE(NOW(),-1)),A.feedback_num,B.rescue_num,C.maint_num1 + D.maint_num2 + E.maint_num3 + F.maint_num4,G.inspection_num FROM  "
			+ "(SELECT COUNT(1) feedback_num FROM ELEVATOR_APPRAISAL WHERE (EVALUATE_TYPE = '1' OR (EVALUATE_TYPE = '2' AND IS_LIKE = '1')) AND QUARTER(APPRAISAL_TIME) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(APPRAISAL_TIME) = YEAR(ADDDATE(NOW(),-1))) A, "
			+ "(SELECT COUNT(1) rescue_num FROM EMERGENCY_RESCUE WHERE QUARTER(RESCUE_END_TIME) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(RESCUE_END_TIME) = YEAR(ADDDATE(NOW(),-1))) B, "
			+ "(SELECT COUNT(1) maint_num1 FROM DEBRIS_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(MAINTENANCE_END_DATE) = YEAR(ADDDATE(NOW(),-1))) C, "
			+ "(SELECT COUNT(1) maint_num2 FROM ESCALATOR_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(MAINTENANCE_END_DATE) = YEAR(ADDDATE(NOW(),-1))) D, "
			+ "(SELECT COUNT(1) maint_num3 FROM HYDPRESS_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(MAINTENANCE_END_DATE) = YEAR(ADDDATE(NOW(),-1))) E, "
			+ "(SELECT COUNT(1) maint_num4 FROM TRACTION_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(MAINTENANCE_END_DATE) = YEAR(ADDDATE(NOW(),-1))) F, "
			+ "(SELECT COUNT(1) inspection_num FROM DAILY_INSPECTION WHERE QUARTER(CHECK_END_DT) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(CHECK_END_DT) = YEAR(ADDDATE(NOW(),-1))) G ")
	void insertImplementation() throws Exception;

	/**
	 * 辖区内各项安全状况统计
	 * 
	 * @return
	 * @throws Exception
	 */
	@Select("SELECT quarter,feedback_num feedbackNum,rescue_num rescueNum FROM implementation_statistics WHERE YEAR = #{year}")
	List<Map<String, Object>> selectImplementation1(Map<String, Object> inMap) throws Exception;

	/**
	 * 辖区内各项安全状况统计
	 * 
	 * @return
	 * @throws Exception
	 */
	@Select("SELECT quarter,maint_num maintNum,inspection_num inspectionNum FROM implementation_statistics WHERE YEAR = #{year}")
	List<Map<String, Object>> selectImplementation2(Map<String, Object> inMap) throws Exception;

	/**
	 * 辖区内各项安全状况统计
	 * 
	 * @return
	 * @throws Exception
	 */
	@Select("SELECT A.ID id,A.PARTY_NAME partyName,C.QUARTER quarter,IFNULL(SUM(C.REPAIR_NUM),0) repairNum FROM "
			+ "PARTY A LEFT JOIN SUB_PARTY_INFO B ON A.ID = B.PARENT_ID  "
			+ "LEFT JOIN REPAIR_STATISTICS C ON B.ID = C.PARTY_ID "
			+ "WHERE A.PARENT_ID = #{id} AND A.PARENT_ID != A.ID AND C.YEAR = #{year} "
			+ "GROUP BY A.ID,A.PARTY_NAME,C.QUARTER ")
	List<Map<String, Object>> selectRepair(Map<String, Object> inMap) throws Exception;

	/**
	 * 辖区内各项安全状况统计
	 * 
	 * @return
	 * @throws Exception
	 */
	@Select("SELECT A.ID id,A.PARTY_NAME partyName,C.QUARTER quarter,IFNULL(SUM(C.OVERTIME_NUM),0) overtimeNum FROM "
			+ "PARTY A LEFT JOIN SUB_PARTY_INFO B ON A.ID = B.PARENT_ID  "
			+ "LEFT JOIN MAINT_OVERTIME_STATISTICS C ON B.ID = C.PARTY_ID "
			+ "WHERE A.PARENT_ID = #{id} AND A.PARENT_ID != A.ID AND C.YEAR = #{year} "
			+ "GROUP BY A.ID,A.PARTY_NAME,C.QUARTER ")
	List<Map<String, Object>> selectMaintOvertime(Map<String, Object> inMap) throws Exception;

	/**
	 * 辖区内各项安全状况统计
	 * 
	 * @return
	 * @throws Exception
	 */
	@Select("SELECT A.ID id,A.PARTY_NAME partyName,C.QUARTER quarter,IFNULL(SUM(C.CHECK_NUM),0) checkNum FROM "
			+ "PARTY A LEFT JOIN SUB_PARTY_INFO B ON A.ID = B.PARENT_ID  "
			+ "LEFT JOIN CHECK_OVERTIME_STATISTICS C ON B.ID = C.PARTY_ID "
			+ "WHERE A.PARENT_ID = #{id} AND A.PARENT_ID != A.ID AND C.YEAR = #{year} "
			+ "GROUP BY A.ID,A.PARTY_NAME,C.QUARTER ")
	List<Map<String, Object>> selectCheckOvertime(Map<String, Object> inMap) throws Exception;

	/**
	 * 电梯安全保障工作落实情况
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countMaintRepairNum")
	Map<String, Object> countMaintRepairNum() throws Exception;

	/**
	 * 电梯事故隐患排除情况
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countRepairNum")
	List<Map<String, Object>> countRepairNum(Map<String, Object> inMap) throws Exception;

	@Insert("INSERT INTO repair_statistics (year,quarter,party_id,party_name,repair_num) "
			+ "SELECT YEAR(ADDDATE(NOW(),-1)),QUARTER(ADDDATE(NOW(),-1)),A.ID id,A.PARTY_NAME partyName,IFNULL(SUM(C.NUM),0) RepairNum FROM PARTY A  "
			+ "INNER JOIN PARTY B ON A.ID = B.PARENT_ID  "
			+ "INNER JOIN (SELECT COUNT(1) NUM,REPAIR_PARTY_ID FROM REPAIR_INFO WHERE QUARTER(RESOLVE_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(RESOLVE_DATE) = YEAR(ADDDATE(NOW(),-1)) GROUP BY REPAIR_PARTY_ID) C ON B.ID = C.REPAIR_PARTY_ID  "
			+ "GROUP BY A.ID,A.PARTY_NAME ")
	void insertRepair() throws Exception;

	/**
	 * 电梯安全保障工作超期情况
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countMaintNum")
	List<Map<String, Object>> countMaintNum(Map<String, Object> inMap) throws Exception;

	@Insert("INSERT INTO maint_overtime_statistics (year,quarter,party_id,party_name,overtime_num) "
			+ "SELECT YEAR(ADDDATE(NOW(),-1)),QUARTER(ADDDATE(NOW(),-1)),A.ID id,A.PARTY_NAME partyName,IFNULL(SUM(C.NUM),0) FROM PARTY A  "
			+ "INNER JOIN PARTY B ON A.ID = B.PARENT_ID  "
			+ "INNER JOIN (SELECT A.MAINTENANCE_PARTY_ID,IFNULL(SUM(A.NUM),0) NUM FROM  "
			+ "(SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM DEBRIS_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(MAINTENANCE_END_DATE) = YEAR(ADDDATE(NOW(),-1)) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL  "
			+ "SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM ESCALATOR_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(MAINTENANCE_END_DATE) = YEAR(ADDDATE(NOW(),-1)) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL  "
			+ "SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM HYDPRESS_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(MAINTENANCE_END_DATE) = YEAR(ADDDATE(NOW(),-1)) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID UNION ALL  "
			+ "SELECT COUNT(1) NUM,MAINTENANCE_PARTY_ID FROM TRACTION_HALF_MON WHERE QUARTER(MAINTENANCE_END_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(MAINTENANCE_END_DATE) = YEAR(ADDDATE(NOW(),-1)) AND IS_IN_TIME_MAINT = '2' GROUP BY MAINTENANCE_PARTY_ID) A GROUP BY A.MAINTENANCE_PARTY_ID) C ON B.ID = C.MAINTENANCE_PARTY_ID  "
			+ "GROUP BY A.ID,A.PARTY_NAME ")
	void insertMaintOvertime() throws Exception;

	@Insert("INSERT INTO elevator_overtime (elevator_id,overtime_date) select #{elevatorId}, DATE_FORMAT(NOW(),'%Y%m%d') from dual where EXISTS (select 1 from dual where #{date} < DATE_FORMAT(NOW(),'%Y%m%d'))")
	void insertCheckOvertime(Map<String, Object> param) throws Exception;

	@Insert("INSERT INTO check_overtime_statistics (year,quarter,party_id,party_name,check_num) "
			+ "SELECT YEAR(ADDDATE(NOW(),-1)),QUARTER(ADDDATE(NOW(),-1)),A.ID id,A.PARTY_NAME partyName,IFNULL(SUM(C.NUM),0) FROM PARTY A  "
			+ "INNER JOIN PARTY B ON A.ID = B.PARENT_ID  "
			+ "INNER JOIN "
			+ "(SELECT COUNT(1) NUM,B.MAINTENANCE_PARTY_ID FROM ELEVATOR_OVERTIME A LEFT JOIN ELEVATOR B ON A.ELEVATOR_ID = B.ID WHERE QUARTER(A.OVERTIME_DATE) = QUARTER(ADDDATE(NOW(),-1)) AND YEAR(A.OVERTIME_DATE) = YEAR(ADDDATE(NOW(),-1)) GROUP BY B.MAINTENANCE_PARTY_ID "
			+ ") C ON B.ID = C.MAINTENANCE_PARTY_ID  "
			+ "GROUP BY A.ID,A.PARTY_NAME ")
	void insertCheckOvertime2() throws Exception;

	/**
	 * 辖区内电梯的总数量(物业)
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countElevatorNum")
	List<Map<String, Object>> countElevatorNum(Map<String, Object> inMap) throws Exception;

	/**
	 * 辖区内电梯的总数量(维保)
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countEleMaintNum")
	List<Map<String, Object>> countEleMaintNum(Map<String, Object> inMap) throws Exception;

	/**
	 * 不同类型电梯数量占比
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countEleTypeNum")
	List<Map<String, Object>> countEleTypeNum(Map<String, Object> inMap) throws Exception;

	/**
	 * 使用年限达10年以上电梯数量
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countEleLifeNumOlder")
	List<Map<String, Object>> countEleLifeNumOlder(Map<String, Object> inMap) throws Exception;

	/**
	 * 使用年限达10年以下电梯数量
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countEleLifeNumYounger")
	List<Map<String, Object>> countEleLifeNumYounger(Map<String, Object> inMap) throws Exception;

	/**
	 * 本帐户辖区维保单位数量
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countMaintCompany")
	List<Map<String, Object>> countMaintCompany(Map<String, Object> inMap) throws Exception;

	/**
	 * 各单位 资质 数量 及 占比
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countCompanyQuali")
	List<Map<String, Object>> countCompanyQuali(Map<String, Object> inMap) throws Exception;

	/**
	 * 本帐户辖区使用单位数量
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countUseCompany")
	List<Map<String, Object>> countUseCompany(Map<String, Object> inMap) throws Exception;

	/**
	 * 各星级 维保单位总数及占比
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countStarCompany")
	List<Map<String, Object>> countStarCompany(Map<String, Object> inMap) throws Exception;

	/**
	 * 本帐户辖区安全责任主体单位各类型技术人员持证数量
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countTechmanCompany")
	List<Map<String, Object>> countTechmanCompany(Map<String, Object> inMap) throws Exception;

	/**
	 * 乘客投诉处理的反馈 率
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countFeedbackRio")
	List<Map<String, Object>> countFeedbackRio(Map<String, Object> inMap) throws Exception;

	/**
	 * 本帐户辖区整体维保准时率
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countMaintInTime")
	List<Map<String, Object>> countMaintInTime(Map<String, Object> inMap) throws Exception;

	/**
	 * 各类型周期性维保的平均时长
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countTypeMaintTime")
	List<Map<String, Object>> countTypeMaintTime(Map<String, Object> inMap) throws Exception;

	/**
	 * (按月)本帐户辖区周期内整体召修/维修落实率
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countRepair")
	List<Map<String, Object>> countRepair(Map<String, Object> inMap) throws Exception;

	/**
	 * 本帐户辖区周期内现场应急救援累计数
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countRescue")
	List<Map<String, Object>> countRescue(Map<String, Object> inMap) throws Exception;

	/**
	 * 使用单位（物业）应急救援演练总次数
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countRescueDrill")
	List<Map<String, Object>> countRescueDrill(Map<String, Object> inMap) throws Exception;

	/**
	 * 检验机构数量
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countCheckOrgNum")
	List<Map<String, Object>> countCheckOrgNum(Map<String, Object> inMap) throws Exception;

	/**
	 * 人员数量
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countUserNum")
	List<Map<String, Object>> countUserNum(Map<String, Object> inMap) throws Exception;

	/**
	 * 使用单位（物业）过期电梯数
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countOvertimeElevator")
	List<Map<String, Object>> countOvertimeElevator(Map<String, Object> inMap) throws Exception;

	/**
	 * 年度乘客投诉处理的反馈 率
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countFeedbackYear")
	List<Map<String, Object>> countFeedbackYear(Map<String, Object> inMap) throws Exception;

	/**
	 * 本区域 使用单位各资质数量，汇总统计
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countUsedZz")
	List<Map<String, Object>> countUsedZz(Map<String, Object> inMap) throws Exception;

	/**
	 * 本区域 维保单位各资质数量，汇总统计
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countMaintZz")
	List<Map<String, Object>> countMaintZz(Map<String, Object> inMap) throws Exception;

	/**
	 * 辖区内 各类 应急救援发生的次数
	 * 
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countRescueNum")
	List<Map<String, Object>> countRescueNum(Map<String, Object> inMap) throws Exception;

	/**
	 * 查询待通知的物业人员列表
	 * 
	 * @return
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "findNotice")
	List<Map<String, Object>> findNotice() throws Exception;

	/**
	 * 查询电梯，投诉，维修，维保，应急救援，救援演练 年度总数
	 * 
	 * @param map
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "findCountCount")
	List<Map<String, Object>> findCountCount(Map<String, Object> map) throws Exception;

	/**
	 * 查询 微白 电梯，维修，维保， 年度总数
	 * 
	 * @param map
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "findMaintCount")
	List<Map<String, Object>> findMaintCount(Map<String, Object> map) throws Exception;

	/**
	 * 查询 维保单位 今日维保
	 * 
	 * @param map
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "findTodayMaint")
	List<Map<String, Object>> findTodayMaint(Map<String, Object> map) throws Exception;

	/**
	 * 根据sql查数据
	 *
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "selfCount")
	List<Map<String, Object>> selfCount(String sql) throws Exception;

	/**
	 * 市
	 * @param id
	 * @return
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countEleNumCity")
	Map<String, Object> countEleNumCity(String id);

	/**
	 * 局 分局
	 * @param id
	 * @return
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countEleNumArea")
	Map<String, Object> countEleNumArea(String id);

    /**
     * 市
     * @param id
     * @return
     */
    @SelectProvider(type = StatisticsMappSql.class, method = "countYearDistribCity")
    Map countYearDistribCity(String id);


    /**
     * 局 分局
     * @param id
     * @return
     */
    @SelectProvider(type = StatisticsMappSql.class, method = "countYearDistribArea")
    Map countYearDistribArea(String id);


	/**
	 * 市 辖区内电梯年限分布
	 * @param id
	 * @return
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countOperDistribCity")
	Map countOperDistribCity(String id);


	/**
	 * 局 分局 辖区内电梯年限分布
	 * @param id
	 * @return
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countOperDistribArea")
	Map countOperDistribArea(String id);

	/**
	 * 市 电梯事故隐患排除情况
	 * @param id
	 * @return
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countRepairNumCity")
	List<Map<String, Object>> countRepairNumCity(String id);

	/**
	 * 市 电梯事故隐患排除情况
	 * @param id
	 * @return
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countRepairNumArea")
	List<Map<String, Object>> countRepairNumArea(String id);

	/**
	 * 电梯安全保障工作超期情况 局
	 *
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = StatisticsMappSql.class, method = "countMaintNumArea")
	List<Map<String, Object>> countMaintNumArea(Map<String, Object> inMap);
}
